package org.fastsyncer.connector.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.fastsyncer.common.constant.ConnectorConstant;
import org.fastsyncer.common.entity.BaseInfo;
import org.fastsyncer.common.entity.DatabaseConfig;
import org.fastsyncer.connector.common.DatabaseConstant;
import org.fastsyncer.connector.util.DatabaseUtil;
import org.fastsyncer.connector.util.JDBCUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;

public class DatabaseConnector implements Database {

	private final Logger logger = LoggerFactory.getLogger(DatabaseConnector.class);
	
	private static DatabaseConnector instance = null;

	/**
	 * 不建议直接new实例,建议使用DatabaseConnector.getInstance()替换
	 */
	public DatabaseConnector() {
	}

	/**
	 * 建议使用该方法
	 * 
	 * @return DatabaseConnector
	 */
	public static DatabaseConnector getInstance() {
		if (instance == null) {
			synchronized (DatabaseConnector.class) {
				instance = new DatabaseConnector();
			}
		}
		return instance;
	}
	
	/**
	 * 判断URL是否可用
	 * 
	 * @Title: isAlive
	 * @Description: 判断URL是否可用
	 * @param config
	 *            Database配置
	 * @return: boolean true: 可用 false: 不可用
	 */
	public boolean isAlive(DatabaseConfig config){
	    String driver = config.getDriverClassName();
	    String url = config.getUrl();
	    String username = config.getUsername();
	    String password = config.getPassword();
		try {
		    // 如果是Oracle,替换driver类型为PoolDataSource
		    driver = DatabaseConstant.DB_ORACLE_DRIVER_CLASSNAME.equals(driver) ? "oracle.jdbc.pool.OracleConnectionPoolDataSource" : driver;
		    Connection connection = JDBCUtil.getConnection(driver, url, username, password);
	        if(null!=connection){
	            JDBCUtil.close(connection);
	            return true;
	        }
		} catch (Exception e) {
		    logger.error("unable to connect:" + url, e.getMessage());
		}
		return false;
	}
	
	@Override
	public JdbcTemplate getJdbcTemplate(DatabaseConfig config) {
		return DatabaseUtil.getJdbcTemplate(config);
	}
	
	@Override
    public void close(JdbcTemplate jdbcTemplate) {
	    DatabaseUtil.close(jdbcTemplate);
    }
	
	@Override
	public BaseInfo getBaseInfoConnector(DatabaseConfig config) {
		JdbcTemplate jdbcTemplate = this.getJdbcTemplate(config);
		// 只查询元信息
		String metaSQL = "SELECT * FROM " + config.getTable().toUpperCase();
		String countSQL = metaSQL;
		switch (config.getDriverClassName()) {
		case DatabaseConstant.DB_MYSQL_DRIVER_CLASSNAME:
			// Mysql
			metaSQL += DatabaseConstant.DB_MYSQL_PAGE_SQL_NULL;
			break;
		case DatabaseConstant.DB_ORACLE_DRIVER_CLASSNAME:
			// Oracle
			metaSQL += DatabaseConstant.DB_ORACLE_PAGE_SQL_NULL;
			break;
		default:
			break;
		}
		BaseInfo connector = DatabaseUtil.getDataSourceConnector(jdbcTemplate, metaSQL, countSQL);
		// 释放连接
		this.close(jdbcTemplate);
		return connector;
	}

	@Override
	public BaseInfo getBaseInfoConnector(DatabaseConfig config, String queryFilter) {
		JdbcTemplate jdbcTemplate = this.getJdbcTemplate(config);
		// 查询元信息
		String metaSQL = "SELECT * FROM " + config.getTable().toUpperCase();
		// 求数据总量
		String countSQL = metaSQL + queryFilter;

		switch (config.getDriverClassName()) {
		case DatabaseConstant.DB_MYSQL_DRIVER_CLASSNAME:
			// Mysql
			metaSQL += DatabaseConstant.DB_MYSQL_PAGE_SQL_NULL;
			break;
		case DatabaseConstant.DB_ORACLE_DRIVER_CLASSNAME:
			// Oracle
			metaSQL += DatabaseConstant.DB_ORACLE_PAGE_SQL_NULL;
			break;
		default:
			break;
		}

		BaseInfo connector = DatabaseUtil.getDataSourceConnector(jdbcTemplate, metaSQL, countSQL);
        // 释放连接
		this.close(jdbcTemplate);
		return connector;
	}

	@Override
	public String getJdbcSQL(String opertion, DatabaseConfig config, List<HashMap<String, Object>> fileds,
			String queryFilter) {
	    // 去掉重复的查询字段
	    List<String> filedNames = new ArrayList<String>();
	    for (HashMap<String, Object> obj : fileds) {
            String name = (String) obj.get("name");
            // 如果没有重复
            if(!filedNames.contains(name)){
                filedNames.add(name);
            }
        }
	    
		String table = config.getTable().toUpperCase();
		String sql = "";
		switch (opertion) {
		case ConnectorConstant.OPERTION_QUERY:
			String query = "";
			for (String f : filedNames) {
				query += table + "." + f + ", ";
			}
			query = query.substring(0, query.lastIndexOf(","));
			sql = "SELECT " + query + " FROM " + table;
			// 解析查询条件
			sql += StringUtils.isNotBlank(queryFilter) ? queryFilter : "";
			// 分页语句
			sql = this.getPageSQL(config, sql);
			break;
		case ConnectorConstant.OPERTION_INSERT:
			String insertFileds = "";
			String insertValues = "";
            for (String f : filedNames) {
				insertFileds += table + "." + f + ", ";
				insertValues += "?, ";
			}
			insertFileds = insertFileds.substring(0, insertFileds.lastIndexOf(","));
			insertValues = insertValues.substring(0, insertValues.lastIndexOf(","));
			sql = "INSERT INTO " + table + "(" + insertFileds + ") VALUES (" + insertValues + ")";
			break;
		case ConnectorConstant.OPERTION_UPDATE:
			String update = "UPDATE " + config.getTable().toUpperCase() + " SET ";
			for (HashMap<String, Object> obj : fileds) {
				String f = (String) obj.get("name");
				update += table + "." + f + "=?,";
			}
			update = update.substring(0, update.lastIndexOf(","));
			sql += update + " WHERE " + table + "." + config.getPrimaryKey().toUpperCase() + "=?";
			break;
		case ConnectorConstant.OPERTION_DELETE:
			String delete = "DELETE FROM " + config.getTable().toUpperCase();
			sql += delete + " WHERE " + table + "." + config.getPrimaryKey().toUpperCase() + "=?";
			break;
		default:
			break;
		}
		return sql;
	}

	/**
	 * 解析查询条件
	 * 
	 * @param query
	 * @return
	 */
	public String getFilter(HashMap<String, List<HashMap<String, String>>> query) {
		if (query != null && query.size() > 0) {
			String conditionCMD = "";
			// 拼接SQL
			List<HashMap<String, String>> addList = query.get(ConnectorConstant.OPERTION_QUERY_AND);
			String addSql = this.getOperSQL(ConnectorConstant.OPERTION_QUERY_AND, addList);
			conditionCMD += addSql;

			// 拼接SQL
			List<HashMap<String, String>> orList = query.get(ConnectorConstant.OPERTION_QUERY_OR);
			String orSql = this.getOperSQL(ConnectorConstant.OPERTION_QUERY_OR, orList);
			// 如果已有Add条件
			conditionCMD += StringUtils.isNotBlank(addSql) && StringUtils.isNotBlank(orSql) ? " OR " + orSql : orSql;

			return StringUtils.isNotBlank(conditionCMD) ? " WHERE " + conditionCMD : "";
		}
		return "";
	}

	/**
	 * 根据过滤条件获取查询SQL
	 * 
	 * @param operator
	 * @param list
	 * @return
	 */
	private String getOperSQL(String operator, List<HashMap<String, String>> list) {
		String sql = "";
		if (list != null && list.size() > 0) {
			sql += "(";
			int size = list.size();
			for (int i = 0; i < size; i++) {
				HashMap<String, String> c = list.get(i);
				String oper = c.get("operator");
				sql += c.get("name") + ConnectorConstant.OPERS.get(oper) + "'" + c.get("value") + "'";
				sql += i < size - 1 ? " " + operator + " " : "";
			}
			sql += ")";
		}
		return sql;
	}

	/**
	 * 根据数据源类型获取分页SQL语句
	 * 
	 * @param config
	 * @return
	 */
	public String getPageSQL(DatabaseConfig config, String sql) {
		String driver = config.getDriverClassName().toLowerCase();
		// Mysql 分页查询
		if (driver.equalsIgnoreCase(DatabaseConstant.DB_MYSQL_DRIVER_CLASSNAME)) {
			return sql += DatabaseConstant.DB_MYSQL_PAGE_SQL;
		}
		// Oracle 分页查询
		if (driver.equalsIgnoreCase(DatabaseConstant.DB_ORACLE_DRIVER_CLASSNAME)) {
			return DatabaseConstant.DB_ORACLE_PAGE_SQL_START + sql + DatabaseConstant.DB_ORACLE_PAGE_SQL_END;
		}
		return "";
	}

	@Override
	public void batchRowsSetter(PreparedStatement ps, List<HashMap<String, Object>> fileds, Map<String, Object> row) {
		int filedSize = fileds.size();
		for (int i = 0; i < filedSize; i++) {
			// 取出字段和对应值
			HashMap<String, Object> filed = fileds.get(i);
			int type = Integer.parseInt(filed.get("type").toString());
			Object val = row.get(filed.get("name"));
			DatabaseUtil.preparedStatementSetter(ps, i + 1, type, val);
		}
	}

}
